package com.pizza.database.mapping;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.pizza.database.DBConnection;
import com.pizza.model.Commande;
import com.pizza.model.Diametre;
import com.pizza.model.Ingredient;
import com.pizza.model.Pizza;

/**
 * Class PizzaDB, mapping avec la bdd.
 *
 */
public class PizzaDB implements Serializable {

	private DBConnection dbConnexion;

	public PizzaDB(DBConnection _dbConnexion) {
		dbConnexion = _dbConnexion;
	}

	public Pizza getPizzaById(int _id) {
		Connection connexion = dbConnexion.getConnection();
		Pizza retour = null;
		StringBuilder query = new StringBuilder();
		query.append("SELECT * ");
		query.append(" FROM pizzas");
		query.append(" WHERE id=?");

		StringBuilder queryDiametres = new StringBuilder();
		queryDiametres.append("SELECT id_diametres, prix");
		queryDiametres.append(" FROM pizzas_diametres");
		queryDiametres.append(" WHERE id_pizzas=?");

		StringBuilder queryIngredients = new StringBuilder();
		queryIngredients.append("SELECT id_ingredients");
		queryIngredients.append(" FROM pizzas_ingredients");
		queryIngredients.append(" WHERE id_pizzas=?");
		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());
			pStatement.setInt(1, _id);
			ResultSet rs = pStatement.executeQuery();
			//rs.first();
			while (rs.next()) {
				String nom = rs.getString("nom");
				String description = rs.getString("description");
				String url_image = rs.getString("url_image");
				int id_client_pizza_perso = rs.getInt("id_clients");
				boolean pizza_personnalisee = (id_client_pizza_perso != 0);
				retour = new Pizza(_id, nom, description, url_image,
						pizza_personnalisee, new ClientDB(dbConnexion)
								.getClientById(id_client_pizza_perso));

				pStatement = connexion.prepareStatement(queryDiametres
						.toString());
				pStatement.setInt(1, _id);
			}
			rs = pStatement.executeQuery();
			while (rs.next()) {
				int id_diametres = rs.getInt("id_diametres");
				float prix = rs.getFloat("prix");
				retour.addDiametre(new DiametreDB(dbConnexion)
						.getDiametreById(id_diametres), prix);

			}
			pStatement.close();

			pStatement = connexion
					.prepareStatement(queryIngredients.toString());
			pStatement.setInt(1, _id);
			rs = pStatement.executeQuery();
			while (rs.next()) {
				int id_ingredients = rs.getInt("id_ingredients");
				retour.addIngredient(new IngredientDB(dbConnexion)
						.getIngredientById(id_ingredients));
			}
			// retour.setEntrySet(new
			// ArrayList(retour.getDiametres().entrySet()));
			pStatement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}

	public int insertPizza(Pizza _pizza) {
		int retour = 0;

		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO pizzas");
		query.append(" (nom, description, url_image, id_clients)");
		query.append(" VALUES(?,?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setString(1, _pizza.getNom());
			pStatement.setString(2, _pizza.getDescription());
			pStatement.setString(3, _pizza.getUrl_image());
			if (_pizza.getClientPizzaPerso() != null)
				pStatement.setInt(4, _pizza.getClientPizzaPerso().getId());
			else
				pStatement.setInt(4, 0);

			// Insertion de la pizza
			retour += pStatement.executeUpdate();

			ResultSet clefs = pStatement.getGeneratedKeys();

			if (clefs.next()) {
				Long id = (Long) clefs.getObject(1);
				_pizza.setId(id.intValue());
			}
			clefs.close();
			pStatement.close();

			for(int i=0; i < _pizza.getDiametres().size(); i++) {
				insertPizzasDiametre(_pizza, _pizza.getDiametres().get(i).getDiametre(), _pizza.getDiametres().get(i).getPrix());				
			}
			for(int i=0; i < _pizza.getIngredients().size(); i++) {
				insertPizzasIngredient(_pizza, _pizza.getIngredients().get(i));				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return _pizza.getId();
	}
	
	public int insertPizzasDiametre(Pizza _pizza, Diametre _diametre, float _prix) {
		int retour = 0;

		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO pizzas_diametres");
		query.append(" (id_pizzas, id_diametres, prix)");
		query.append(" VALUES(?,?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _pizza.getId());
			pStatement.setInt(2, _diametre.getId());
			pStatement.setFloat(3, _prix);
			
			// Insertion du diametre de la pizza
			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}
	
	public int insertPizzasIngredient(Pizza _pizza, Ingredient _ingredient) {
		int retour = 0;

		Connection connexion = dbConnexion.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO pizzas_ingredients");
		query.append(" (id_pizzas, id_ingredients)");
		query.append(" VALUES(?,?)");

		try {
			PreparedStatement pStatement = connexion.prepareStatement(query
					.toString());

			pStatement.setInt(1, _pizza.getId());
			pStatement.setInt(2, _ingredient.getId());
			
			// Insertion de l'ingredient de la pizza
			retour += pStatement.executeUpdate();

			pStatement.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return retour;
	}

}
